﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Threading;
using System.Windows.Forms;
using System.Xml.Linq;
using System.Collections.Generic;

namespace GeneratePE {
    public partial class frmGenerate : Form {

        public frmGenerate() {
            InitializeComponent();

            GenerateHelper.IsSqlServer = true;
            this.cbProcedures.Checked = this.cbParams.Checked = this.cbEntities.Checked = true;
            this.btnGenerate.Enabled = this.btnAbort.Enabled = false;
            this.InitUserConfig();
        }

        #region Helps and methods

        // 载入默认的配置文件
        private void InitUserConfig() {
            XDocument xd = XDocument.Load(
                string.Format(@"{0}\UserConfig.xml", Application.StartupPath));
            XElement xDataBase = xd.Root.Elements().First(x => { return x.Name == "DataBase"; });
            XElement xEntity = xd.Root.Elements().First(x => { return x.Name == "Entity"; });
            XElement xGenerate = xd.Root.Elements().First(x => { return x.Name == "Generate"; });
            // 数据库
            this.rbSqlServer.Checked = xDataBase.Attribute(XName.Get("Type")).Value == "SqlServer";
            this.rbAccess.Checked = xDataBase.Attribute(XName.Get("Type")).Value == "Access";
            this.tbServer.Text = xDataBase.Element(XName.Get("Server")).Value;
            this.cbDatabase.Text = this.tbAccessFilePath.Text =
                xDataBase.Element(XName.Get("Database")).Value;
            this.tbUser.Text = this.tbUserAccessFile.Text =
                xDataBase.Element(XName.Get("User")).Value;
            this.tbPassword.Text = this.tbPasswordAccessFile.Text =
                xDataBase.Element(XName.Get("Password")).Value;
            // 实体
            this.tbNamespace.Text = xEntity.Element(XName.Get("Namespace")).Value;
            this.tbPrefix.Text = xEntity.Element(XName.Get("ClassPrefix")).Value;
            this.cbTidy.Checked = bool.Parse(xEntity.Element(XName.Get("Tidy")).Value);
            this.cbParameter.Checked = bool.Parse(xEntity.Element(XName.Get("Parameter")).Value);
            this.cbSummary.Checked = bool.Parse(xEntity.Element(XName.Get("Summary")).Value);
            this.cbDataReader.Checked = bool.Parse(xEntity.Element(XName.Get("Datareader")).Value);
            // 生成项
            this.tbSavePath.Text = xGenerate.Element(XName.Get("SavePath")).Value;
            this.cbProcedures.Checked = bool.Parse(xGenerate.Element(XName.Get("Procedure")).Value);
            this.cbParams.Checked = bool.Parse(xGenerate.Element(XName.Get("Entity")).Value);
            this.cbEntities.Checked = bool.Parse(xGenerate.Element(XName.Get("Parameter")).Value);

            if (this.rbSqlServer.Checked) { this.radio_Click(this.rbSqlServer, EventArgs.Empty); }
            if (this.rbAccess.Checked) { this.radio_Click(this.rbAccess, EventArgs.Empty); }
            this.cbGenerate_Click(null, EventArgs.Empty);
        }
        // 写入默认的配置文件
        private void SaveUserConfig() {
            XDocument xd = XDocument.Load(
                string.Format(@"{0}\UserConfig.xml", Application.StartupPath));
            XElement xDataBase = xd.Root.Elements().First(x => { return x.Name == "DataBase"; });
            XElement xEntity = xd.Root.Elements().First(x => { return x.Name == "Entity"; });
            XElement xGenerate = xd.Root.Elements().First(x => { return x.Name == "Generate"; });
            // 数据库
            if (this.rbSqlServer.Checked) {
                xDataBase.SetAttributeValue(XName.Get("Type"), "SqlServer");
                xDataBase.SetElementValue(XName.Get("Server"), this.tbServer.Text);
                xDataBase.SetElementValue(XName.Get("Database"), this.cbDatabase.Text);
                xDataBase.SetElementValue(XName.Get("User"), this.tbUser.Text);
                xDataBase.SetElementValue(XName.Get("Password"), this.tbPassword.Text);
            }
            else {
                xDataBase.SetAttributeValue(XName.Get("Type"), "Access");
                xDataBase.SetElementValue(XName.Get("Server"), ".");
                xDataBase.SetElementValue(XName.Get("Database"), this.tbAccessFilePath.Text);
                xDataBase.SetElementValue(XName.Get("User"), this.tbUserAccessFile.Text);
                xDataBase.SetElementValue(XName.Get("Password"), this.tbPasswordAccessFile.Text);
            }
            // 实体
            xEntity.SetElementValue(XName.Get("Namespace"), this.tbNamespace.Text);
            xEntity.SetElementValue(XName.Get("ClassPrefix"), this.tbPrefix.Text);
            xEntity.SetElementValue(XName.Get("Tidy"), this.cbTidy.Checked.ToString().ToLower());
            xEntity.SetElementValue(XName.Get("Parameter"), this.cbParameter.Checked.ToString().ToLower());
            xEntity.SetElementValue(XName.Get("Summary"), this.cbSummary.Checked.ToString().ToLower());
            xEntity.SetElementValue(XName.Get("Datareader"), this.cbDataReader.Checked.ToString().ToLower());
            // 生成项
            xGenerate.SetElementValue(XName.Get("SavePath"), this.tbSavePath.Text);
            xGenerate.SetElementValue(XName.Get("Procedure"), this.cbProcedures.Checked.ToString().ToLower());
            xGenerate.SetElementValue(XName.Get("Entity"), this.cbParams.Checked.ToString().ToLower());
            xGenerate.SetElementValue(XName.Get("Parameter"), this.cbEntities.Checked.ToString().ToLower());
            xd.Save(string.Format(@"{0}\UserConfig.xml", Application.StartupPath));
        }
        // 检测合法性
        private bool CheckValidty(string text, string msg) {
            if (string.IsNullOrEmpty(text)) {
                this.ShowMessageAlert(msg);
                return false;
            }
            return true;
        }
        // 提示信息
        private void ShowMessageAlert(string msg) {
            this.statusMsg.Text = msg;
            MessageBox.Show(msg, "Prompting message");
        }
        // 警告信息
        private void ShowMessageWaring(string msg) {
            this.statusMsg.Text = msg;
            MessageBox.Show(msg, "Warning message");
        }
        // 加入一个表
        private void AddTable() {
            foreach (var item in this.lbOwnTable.SelectedItems) {
                this.lbSelectTable.Items.Add(item);
            }
            foreach (var item in this.lbSelectTable.Items) {
                this.lbOwnTable.Items.Remove(item);
            }
            this.SetTableButtonEnabled();
        }
        // 加入多个表
        private void AddAllTable() {
            foreach (var item in this.lbOwnTable.Items) {
                this.lbSelectTable.Items.Add(item);
            }
            this.lbOwnTable.Items.Clear();
            this.SetTableButtonEnabled();
        }
        // 删除一个表
        private void DeleteTable() {
            foreach (var item in this.lbSelectTable.SelectedItems) {
                this.lbOwnTable.Items.Add(item);
            }
            foreach (var item in this.lbOwnTable.Items) {
                this.lbSelectTable.Items.Remove(item);
            }
            this.SetTableButtonEnabled();
        }
        // 删除多个表
        private void DeleteAllTable() {
            foreach (var item in this.lbSelectTable.Items) {
                this.lbOwnTable.Items.Add(item);
            }
            this.lbSelectTable.Items.Clear();
            this.SetTableButtonEnabled();
        }
        // 设置全部添加和全部删除按钮 Enabled
        private void SetTableButtonEnabled() {
            this.btnAdd.Enabled = this.lbOwnTable.SelectedItems.Count > 0;
            this.btnDelete.Enabled = this.lbSelectTable.SelectedItems.Count > 0;
            this.btnAddAll.Enabled = this.lbOwnTable.Items.Count > 0;
            this.btnDeleteAll.Enabled = this.lbSelectTable.Items.Count > 0;
            this.btnGenerate.Enabled = this.lbSelectTable.Items.Count > 0;
        }
        // 重置表格选取操作
        private void ResetTableButton() {
            this.lbOwnTable.Items.Clear();
            this.lbSelectTable.Items.Clear();
            this.dgvFieldList.DataSource = null;
            this.SetTableButtonEnabled();
        }
        // 生成操作时禁用或完成操作后启用
        private void EnadbledOrNot(bool flag) {
            this.btnGenerate.Enabled = flag;
            this.btnAbort.Enabled = !flag;
            this.statusProcess.Visible = !flag;
            this.statusProcess.Value = 0;
            if (GenerateHelper.IsSqlServer) { this.cbProcedures.Enabled = flag; }
            this.cbEntities.Enabled = flag;
            this.cbParams.Enabled = flag;
            this.gbDatabaseConnection.Enabled = flag;
            this.gbTables.Enabled = flag;
            this.gbSaveOption.Enabled = flag;
            this.gbEntities.Enabled = flag;
            if (this.cbTidy.Checked) { this.cbTidy_CheckedChanged(this.cbTidy, null); }
        }

        #endregion

        # region Database operation

        // 连接 SqlServer
        private void ConnectionSqlServer() {
            const string serverMsg = "Database server is mandatory";
            const string databaseMsg = "Database name is mandatory";
            const string userMsg = "Database user name is mandatory";

            string server = this.tbServer.Text;
            string database = this.cbDatabase.Text;
            string user = this.tbUser.Text;
            string password = this.tbPassword.Text;

            if (!this.CheckValidty(server, serverMsg) ||
                !this.CheckValidty(database, databaseMsg) ||
                !this.CheckValidty(user, userMsg)) {
                return;
            }
            // 连接数据库
            try {
                using (SqlConnection conn = new SqlConnection()) {
                    string connnectionString = string.Format(
                        OtherConst.SqlConnectionString, server, database, user, password);
                    conn.ConnectionString = connnectionString;
                    conn.Open();
                    conn.Close();
                    // 设置数据访问连接字符串
                    GenerateHelper.ConnectionString = connnectionString;
                    this.ShowMessageAlert("Database connection sucess!");
                    this.LoadTableListSqlServer();
                    this.btnAddAll.Enabled = true;
                }
            }
            catch {
                this.ShowMessageAlert("Database connection failure!");
            }
        }
        // 连接 Access
        private void ConnectionAccess() {
            const string accessFileMsg = "Access database file is mandatory";
            const string userMsg = "Database user name is mandatory";

            string accessFile = this.tbAccessFilePath.Text;
            string user = this.tbUserAccessFile.Text;
            string password = this.tbPasswordAccessFile.Text;

            if (!this.CheckValidty(accessFile, accessFileMsg) ||
                !this.CheckValidty(user, userMsg)) {
                return;
            }
            // 连接数据库
            try {
                using (OleDbConnection conn = new OleDbConnection()) {
                    string connnectionString = string.Format(
                        OtherConst.AccConnectionString, accessFile, user, password);
                    conn.ConnectionString = connnectionString;
                    conn.Open();
                    conn.Close();
                    // 设置数据访问连接字符串
                    GenerateHelper.ConnectionString = connnectionString;
                    this.ShowMessageAlert("Database connection sucess!");
                    this.LoadTableListAccess();
                    this.btnAddAll.Enabled = true;
                }
            }
            catch {
                this.ShowMessageAlert("Database connection failure!");
            }
        }
        // 载入当前连接的所有表 Sql Server
        private void LoadTableListSqlServer() {
            this.lbOwnTable.Items.Clear();
            this.lbSelectTable.Items.Clear();
            using (SqlConnection conn = new SqlConnection(GenerateHelper.ConnectionString)) {
                conn.Open();
                string[] restriction = new string[] { null, null, null, "BASE TABLE" };
                DataTable dt = conn.GetSchema("Tables", restriction);
                foreach (DataRow dr in dt.Rows) {
                    this.lbOwnTable.Items.Add(dr["TABLE_NAME"]);
                }
            }
        }
        // 载入当前连接的所有表 Access
        private void LoadTableListAccess() {
            this.lbOwnTable.Items.Clear();
            this.lbSelectTable.Items.Clear();
            using (OleDbConnection conn = new OleDbConnection(GenerateHelper.ConnectionString)) {
                conn.Open();
                string[] restriction = new string[] { null, null, null, "TABLE" };
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, restriction);
                foreach (DataRow dr in dt.Rows) {
                    this.lbOwnTable.Items.Add(dr["TABLE_NAME"]);
                }
            }
        }
        // 生成
        private delegate void DelegateGenerate();
        private Thread t;
        private void GenerateCode() {
            string path = @"{0}\{1}.{2}";
            double percent = 0.00, maxPercent = this.lbSelectTable.Items.Count;
            foreach (string item in this.lbSelectTable.Items) {
                if (this.cbProcedures.Checked) {
                    // sql
                    var list = new ProcedureHelper(item).Generate();

                    File.WriteAllText(string.Format(path, this.tbSavePath.Text, item, "sql"), list);
                }
                var peIns = new EntityHelper() {
                    Table = GenerateHelper.LoadTableSchema(item),
                    NameSpace = this.tbNamespace.Text,
                    ClassName = this.tbPrefix.Text + item,
                    IsDataReader = this.cbDataReader.Checked,
                    IsSummary = this.cbSummary.Checked
                };
                if (this.cbEntities.Checked) {
                    // cs
                    var listCS = peIns.GenerateEntity();

                    File.WriteAllText(string.Format(path, this.tbSavePath.Text, item + "Model", "cs"), listCS);
                }
                if (this.cbParams.Checked) {
                    // parameters txt
                    var listParams = this.cbParameter.Checked
                        ? peIns.GenerateParametersStand()
                        : peIns.GenerateParameters();

                    File.WriteAllText(string.Format(path, this.tbSavePath.Text, item, "txt"), listParams);
                }


                this.ChangeProcessBarValue((int)(percent++ / maxPercent * 100));
            }


            this.GenerateDone("Generate procedure and entity code sucess!");
        }

        #endregion

        #region Event handling

        private void radio_Click(object sender, EventArgs e) {
            // 显示位置 Y = 36
            const int yShowPos = 36;
            // 隐藏位置 Y = 100
            const int yHidePos = 100;
            RadioButton rb = (RadioButton)sender;
            switch (rb.Name.ToLower()) {
                case "rbsqlserver":
                    this.pnlSqlServer.Location = new Point(this.pnlSqlServer.Location.X, yShowPos);
                    this.pnlAccess.Location = new Point(this.pnlAccess.Location.X, yHidePos);
                    this.cbProcedures.Checked = this.cbProcedures.Enabled = true;
                    GenerateHelper.IsSqlServer = true;
                    break;
                case "rbaccess":
                    this.pnlSqlServer.Location = new Point(this.pnlSqlServer.Location.X, yHidePos);
                    this.pnlAccess.Location = new Point(this.pnlAccess.Location.X, yShowPos);
                    this.cbProcedures.Checked = this.cbProcedures.Enabled = false;
                    GenerateHelper.IsSqlServer = false;
                    break;
            }
            this.ResetTableButton();
            this.cbDatabase.DataSource = null;
        }

        private void btn_Click(object sender, EventArgs e) {
            Button btn = (Button)sender;
            switch (btn.Name.ToLower()) {
                case "btnconnection":
                    this.ResetTableButton();
                    if (GenerateHelper.IsSqlServer) {
                        this.ConnectionSqlServer();
                    }
                    else {
                        this.ConnectionAccess();
                    }
                    break;
                case "btnaccessfile":
                    this.tbAccessFilePath.Text = this.ofdSelect.ShowDialog() == DialogResult.OK
                        ? this.ofdSelect.FileName
                        : string.Empty;
                    break;
                case "btnadd":
                    this.AddTable(); break;
                case "btndelete":
                    this.DeleteTable(); break;
                case "btnaddall":
                    this.AddAllTable(); break;
                case "btndeleteall":
                    this.DeleteAllTable(); break;
                case "btnbrowse":
                    this.tbSavePath.Text = this.fbdSave.ShowDialog() == DialogResult.OK
                        ? this.fbdSave.SelectedPath
                        : string.Empty;
                    break;
                case "btnabort":
                    this.EnadbledOrNot(true);
                    this.t.Abort();
                    this.GenerateDone("Generate procedure and entity code canceled!");
                    break;
                case "btngenerate":
                    if (string.IsNullOrEmpty(this.tbSavePath.Text)) {
                        this.ShowMessageWaring("Please select save folder path!");
                        return;
                    }
                    this.EnadbledOrNot(false);
                    t = new Thread(new ThreadStart(this.GenerateCode));
                    t.IsBackground = true;
                    t.Start();
                    break;
            }
        }

        private delegate void dProcess(int value);
        private void ChangeProcessBarValue(int value) {
            if (this.InvokeRequired) {
                this.Invoke(new dProcess(this.ChangeProcessBarValue), value);
            }
            else {
                this.statusProcess.Value = value;
                this.statusMsg.Text = string.Format(" {0}%", value);
            }
        }

        private delegate void dGenerateDone(string msg);
        private void GenerateDone(string msg) {
            if (this.InvokeRequired) {
                this.Invoke(new dGenerateDone(this.GenerateDone), msg);
            }
            else {
                this.SaveUserConfig();
                this.EnadbledOrNot(true);
                this.ShowMessageWaring(msg);
            }
        }

        private void lb_DoubleClick(object sender, EventArgs e) {
            ListBox lb = (ListBox)sender;
            switch (lb.Name.ToLower()) {
                case "lbowntable":
                    this.AddTable(); break;
                case "lbselecttable":
                    this.DeleteTable(); break;
            }
            this.lb_Click(lb, e);
        }

        private void lb_Click(object sender, EventArgs e) {
            ListBox lb = (ListBox)sender;
            switch (lb.Name.ToLower()) {
                case "lbowntable":
                    this.btnAdd.Enabled = this.lbOwnTable.SelectedIndex >= 0;
                    if (this.lbOwnTable.SelectedItem != null) {
                        string tableName = this.lbOwnTable.SelectedItem.ToString();
                        try {
                            this.dgvFieldList.DataSource = GenerateHelper.LoadTableSchema(tableName);
                        }
                        catch (Exception ex) {
                            this.ShowMessageAlert("Error! " + ex.Message);
                        }
                    }
                    break;
                case "lbselecttable":
                    this.btnDelete.Enabled = this.lbSelectTable.SelectedIndex >= 0;
                    break;
            }
        }

        private void cbDatabase_Enter(object sender, EventArgs e) {
            const string serverMsg = "Database server is mandatory";
            const string userMsg = "Database user name is mandatory";

            string server = this.tbServer.Text;
            string user = this.tbUser.Text;
            string password = this.tbPassword.Text;

            if (!this.CheckValidty(server, serverMsg) ||
                !this.CheckValidty(user, userMsg)) {
                return;
            }
            try {
                using (SqlConnection conn = new SqlConnection()) {
                    conn.ConnectionString = string.Format(
                         OtherConst.SqlConnectionString, server, "master", user, password);
                    conn.Open();

                    this.cbDatabase.DataSource = conn.GetSchema("Databases");
                    this.cbDatabase.DisplayMember = "DATABASE_NAME";
                    //using (SqlCommand cmd = new SqlCommand("select name from master.sys.databases")) {
                    //    cmd.Connection = conn;
                    //    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    //    DataTable dt = new DataTable();
                    //    da.Fill(dt);
                    //    this.cbDatabase.DataSource = dt;
                    //    this.cbDatabase.DisplayMember = "DATABASE_NAME";
                    //}
                }
            }
            catch (Exception ex) {
                this.cbDatabase.Items.Clear();
                this.statusMsg.Text = "Database connection failure! " + ex.Message;
            }
        }

        private void cbTidy_CheckedChanged(object sender, EventArgs e) {
            this.cbDataReader.Checked = this.cbSummary.Checked = false;
            this.cbDataReader.Enabled = this.cbSummary.Enabled = !this.cbTidy.Checked;
        }

        private void frmGenerate_FormClosing(object sender, FormClosingEventArgs e) {
            if (this.btnAbort.Enabled) { this.btn_Click(this.btnAbort, EventArgs.Empty); }
        }

        private void cbGenerate_Click(object sender, EventArgs e) {
            this.btnGenerate.Enabled =
                this.cbProcedures.Checked |
                this.cbParams.Checked |
                this.cbEntities.Checked;
        }

        #endregion

    }
}
